package com.ptswitch.material.service.impl;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.hxzon.project.AppRuntimeException;
import org.hxzon.util.ApacheZipUtil;
import org.hxzon.util.BitUtil;
import org.hxzon.util.ExcelUtil;
import org.hxzon.util.OgnlUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import com.ptswitch.material.model.material.Bom;
import com.ptswitch.material.model.material.BomMaterial;
import com.ptswitch.material.model.material.BomOrigFile;
import com.ptswitch.material.model.material.Material;
import com.ptswitch.material.service.MaterialExcelService;
import com.ptswitch.material.util.ColumnMap;
import com.ptswitch.material.util.MaterialUtil;

@Service
public class MaterialExcelServiceImpl extends ExcelUtil implements MaterialExcelService {
    private static final Logger logger = LoggerFactory.getLogger(MaterialExcelServiceImpl.class);

    public void buildBomFromExcel(Bom bom, Workbook wb) {
        if (wb == null) {
            return;
        }
        int sheetNum = wb.getNumberOfSheets();
        List<BomMaterial> bomMaterials = new ArrayList<BomMaterial>();
        BomMaterial bomMaterial = null;
        Sheet sheet;
        for (int j = 0; j < sheetNum; j++) {
            sheet = wb.getSheetAt(j);
            if (sheet == null) {
                continue;
            }
            ColumnMap columnMap = MaterialUtil.buildColumnMap(sheet);
            if (columnMap.get("name") != -1) {
                throw AppRuntimeException.error("找到‘物料名称’列，请确认导入的是Bom表，而不是物料表");
            }
            Row row = null;
            int lastrow = sheet.getLastRowNum();
            for (int i = 1; i <= lastrow; i++) {
                row = sheet.getRow(i);
                if (isEmptyRow(row)) {
                    continue;
                }
                bomMaterial = new BomMaterial();
                rowToBomMaterial(row, bomMaterial, columnMap);
                bomMaterial.setBom(bom);
                bomMaterials.add(bomMaterial);
            }
        }
        bom.setBomMaterials(bomMaterials);
    }

    public void buildBomFromExcel(Bom bom, String filePath) {
        Workbook wb = read(filePath);
        buildBomFromExcel(bom, wb);
    }

    public void buildMaterialsFromExcel(List<Material> materials, Workbook wb) {
        if (wb == null) {
            return;
        }
        int sheetNum = wb.getNumberOfSheets();
        Sheet sheet;
        for (int j = 0; j < sheetNum; j++) {
            sheet = wb.getSheetAt(j);
            if (sheet == null) {
                continue;
            }
            ColumnMap columnMap = MaterialUtil.buildColumnMap(sheet);
            if (columnMap.get("name") == -1) {
                throw AppRuntimeException.error("找不到‘物料名称’列，请确认导入的是物料表，而不是Bom表");
            }
            Row row = null;
            Material material = null;
            String code = null;
            int lastrow = sheet.getLastRowNum();
            for (int i = 1; i <= lastrow; i++) {
                row = sheet.getRow(i);
                code = getCode(row, columnMap);
                if (code.isEmpty()) {// fixme not code don't add
                    logger.debug("row {} is no fine code", i);
                    continue;
                }
                material = new Material();
                rowToMaterial(row, material, columnMap);
                materials.add(material);
            }
        }
    }

    public void buildMaterialsFromExcel(List<Material> materials, String filePath) {
        Workbook wb = read(filePath);
        buildMaterialsFromExcel(materials, wb);
    }

    public void buildDataFromZip(List<Material> materials, List<Bom> boms, String parentPath, String zipFilePath) {// parentPath=all/upload
        FileUtils.deleteQuietly(new File(parentPath + "importZip"));
        try {
            FileUtils.forceMkdir(new File(parentPath + "importZip"));
        } catch (IOException e) {
            logger.error(e.getMessage());
        }
        ApacheZipUtil.unzip(parentPath + "importZip", parentPath + zipFilePath);
        Workbook wb = read(parentPath + "importZip/materials.xls");
        buildMaterialsFromExcel(materials, wb);
        wb = read(parentPath + "importZip/boms.xls");
        Sheet sheet = wb.getSheetAt(0);
        int lastrow = sheet.getLastRowNum();
        for (int i = 1; i <= lastrow; i++) {
            Bom bom = new Bom();
            Row row = sheet.getRow(i);
            String fileName = getCellString(row, 3);
            String filePath = "importZip/" + getCellString(row, 3);
            bom.setCode(getCellString(row, 1));
            bom.setVersion(getCellString(row, 2));
            bom.setRemark(getCellString(row, 4));
            bom.setFileName(filePath);
            BomOrigFile upload = new BomOrigFile();
            upload.setFileName(fileName);
            upload.setFilePath(filePath);
            upload.setFileType("application/vnd.ms-excel");
            bom.setOrigFile(upload);
            boms.add(bom);
        }
        for (Bom bom : boms) {
            wb = read(parentPath + bom.getFileName());
            buildBomFromExcel(bom, wb);
        }
    }

    public void outputDataToZip(List<Material> materials, List<Bom> boms, String parentPath, String outputFile) {
        try {
            FileUtils.deleteDirectory(new File(parentPath + "/tmpzip"));
            FileUtils.forceMkdir(new File(parentPath + "/tmpzip"));
        } catch (IOException e) {
            logger.debug(e.getMessage());
        }
        Workbook wb = outputMaterials(materials);
        write(parentPath + "/tmpzip/materials.xls", wb);
        wb = outputBomList(boms, parentPath);
        write(parentPath + "/tmpzip/boms.xls", wb);
        ApacheZipUtil.zip(parentPath + outputFile, parentPath + "/tmpzip");
    }

    //
    public Workbook outputMaterials(List<Material> materials) {
        Workbook wb = newWorkbook();
        Sheet sheet = wb.createSheet();
        Row row = null;
        int rowIndex = 0;
//        setCellString(sheet, rowIndex, 1, 0, 10, "物料列表");
//        rowIndex += 2;
        // title
        row = sheet.createRow(rowIndex);
        setCellString(row, 0, "物料编号");
        setCellString(row, 1, "物料名称");
        setCellString(row, 2, "规格型号");
        setCellString(row, 3, "器件封装");
        setCellString(row, 4, "品牌");
        setCellString(row, 5, "供货商");
        setCellString(row, 6, "器件描述");
        setCellString(row, 7, "PCB封装");
        setCellString(row, 8, "临时编码");
        setCellString(row, 9, "采购状态");
        setCellString(row, 10, "备注");
        rowIndex++;
        // rows
        for (Material material : materials) {
            row = sheet.createRow(rowIndex);
            materialToRow(row, material);
            rowIndex++;
        }
        return wb;
    }

    public void outputMaterials(String absoluteFilePath, List<Material> materials) {
        Workbook wb = outputMaterials(materials);
        write(absoluteFilePath, wb);
    }

    public Workbook outputBom(Bom bom, int outputBomError) {
        Workbook wb = newWorkbook();
        Sheet sheet = wb.createSheet();
        Row row = null;
        // title
        row = sheet.createRow(0);
        setCellString(row, 0, "code");
        setCellString(row, 1, "comment");
        setCellString(row, 2, "designator");
        setCellString(row, 3, "footprint");
        setCellString(row, 4, "voltage");
        setCellString(row, 5, "value");
        setCellString(row, 6, "quantity");
        setCellString(row, 7, "stuffOpt");
        if (outputBomError != MaterialExcelService.OutputBomError_NONE) {
            setCellString(row, 8, "警告");
        }
        // bom materials
        int i = 1;
        for (BomMaterial bomMaterial : bom.getBomMaterials()) {
            row = sheet.createRow(i);
            bomMaterialToRow(row, bomMaterial, outputBomError);
            i++;
        }
        return wb;
    }

    public void outputBom(String absoluteFilePath, Bom bom, int outputBomError) {
        Workbook wb = outputBom(bom, outputBomError);
        write(absoluteFilePath, wb);
    }

    public Workbook outputBomList(List<Bom> boms, String parentPath) {
        Workbook wb = newWorkbook();
        Sheet sheet = wb.createSheet();
        Row row = null;
        int i = 1;
        for (Bom bom : boms) {
            try {
                row = sheet.createRow(i);
                setCellString(row, 1, bom.getCode());
                setCellString(row, 2, bom.getVersion());
                setCellString(row, 3, bom.getFileName());
                setCellString(row, 4, bom.getRemark());
                i++;
                FileUtils.copyFile(new File(parentPath + bom.getFileName()), new File(parentPath + "/tmpzip/" + bom.getFileName()));
            } catch (IOException e) {
                logger.debug(e.getMessage());
            }

        }
        return wb;
    }

    private static Material rowToMaterial(Row row, Material material, ColumnMap columnMap) {
        for (String key : OgnlUtil.getAllStringProperty(Material.class)) {
            OgnlUtil.setValue(material, key, MaterialUtil.getCellString(row, key, columnMap));
        }
        String state = material.getState();
        if (state == null || state.isEmpty()) {
            state = Material.State_Normal;
        }
        if (Material.State_Normal.equals(state) || Material.State_Recommend.equals(state) || Material.State_StopUse.equals(state) || Material.State_UnPublish.equals(state)
                || Material.State_UnRecommend.equals(state)) {

        } else {
            state = state + "描述不规范，请检查";
        }
        material.setState(state);
        return material;
    }

    private static BomMaterial rowToBomMaterial(Row row, BomMaterial bomMaterial, ColumnMap columnMap) {
        for (String key : OgnlUtil.getAllStringProperty(BomMaterial.class)) {
            OgnlUtil.setValue(bomMaterial, key, MaterialUtil.getCellString(row, key, columnMap));
        }
        return bomMaterial;
    }

    private static void materialToRow(Row row, Material material) {
        setCellString(row, 0, material.getCode());
        setCellString(row, 1, material.getName());
        setCellString(row, 2, material.getSpecification());
        setCellString(row, 3, material.getFootprint());
        setCellString(row, 4, material.getBrand());
        setCellString(row, 5, material.getSupplier());
        setCellString(row, 6, material.getDescription());
        setCellString(row, 7, material.getPcbDecal());
        setCellString(row, 8, "");
        setCellString(row, 9, material.getState());
        setCellString(row, 10, material.getRemark());
    }

    private static void bomMaterialToRow(Row row, BomMaterial bomMaterial, int outputBomError) {
        setCellString(row, 0, bomMaterial.getCode());
        setCellString(row, 1, bomMaterial.getComment());
        setCellString(row, 2, bomMaterial.getDesignator());
        setCellString(row, 3, bomMaterial.getFootprint());
        setCellString(row, 4, bomMaterial.getVoltage());
        setCellString(row, 5, bomMaterial.getValue());
        setCellString(row, 6, bomMaterial.getQuantity());
        setCellString(row, 7, bomMaterial.getStuffOpt());
        if (outputBomError == MaterialExcelService.OutputBomError_NONE) {
            return;
        }
        String error = "";
        if (bomMaterial.getCode().isEmpty()) {
            error = "编码为空";
        } else if (bomMaterial.getMaterial() == null) {
            error = "对应物料不存在";
        } else {
            if (BitUtil.isSet(outputBomError, MaterialExcelService.OutputBomError_State)) {
                if (bomMaterial.getMaterial().getState().contains("未发布")) {
                    error = "物料未发布";
                } else if (bomMaterial.getMaterial().getState().contains("不推荐")) {
                    error = "物料不推荐";
                } else if (bomMaterial.getMaterial().getState().contains("停用")) {
                    error = "物料停用";
                } else if (!bomMaterial.getMaterial().getPcbDecal().equals(bomMaterial.getFootprint())) {
                    error = "footprint与物料PCB封装不一致";
                }
            }
            if (BitUtil.isSet(outputBomError, OutputBomError_IndustryLevel)) {
                if (!Material.IndustryLevel_Industry.equals(bomMaterial.getMaterial().getIndustryLevel())) {
                    error = bomMaterial.getMaterial().getIndustryLevel();
                }
            }
        }
        CellStyle style = null;
        if (!error.isEmpty()) {
            style = row.getSheet().getWorkbook().createCellStyle();
            style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
            style.setFillBackgroundColor(IndexedColors.RED.getIndex());
            style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        }
        setCellString(row, 8, error).setCellStyle(style);
    }

    private static String getCode(Row row, ColumnMap columnMap) {
        return MaterialUtil.getCellString(row, "code", columnMap);
    }

    private static Workbook newWorkbook() {
        return new HSSFWorkbook();
    }

}
